=====================
referensi daftar tabel

- kategori
IDKategori	int
NamaKategori	varchar
Deskripsi	text
TipeKategori	enum ['single','double']
StatusKategori	tinyint
GenderRestrict	enum ['male_only','female_only','mix_of_both','no_restriction']
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]

- membership
IDMembership	bigint
IDPlayer	bigint
MembershipNumber	varchar
status	enum
start_date	date
end_date	date
cancel_reason	varchar
counter	int
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]
uq_membership_id	`IDMembership`	UNIQUE	BTREE
IDPlayer	`IDPlayer`	NORMAL	BTREE


- membership_payment
IDMembershipPayment	bigint
IDPlayer	bigint
IDMembershipOld	bigint
IDMembershipNew	bigint
payment_proof	varchar
payment_method	varchar
amount	decimal
status	enum
reject_reason	text
approved_by	varchar
approved_at	datetime
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]

- player
IDPlayer	bigint
email	varchar
password_hash	varchar
full_name	varchar
photo	varchar
phone	varchar
ktp	varchar
birth_place	varchar
birth_date	date
gender	enum
address	text
nationality	varchar
province	varchar
profile_complete	tinyint
verification_status	enum
verification_reject_reason	text
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]
email	`email`	UNIQUE	BTREE
ktp	`ktp`	UNIQUE	BTREE
idx_email	`email`	NORMAL	BTREE
idx_verification_status	`verification_status`	NORMAL	BTREE


- user
IDUser	int
IDGroup	int
Username	varchar
PasswordHash	varchar
NamaLengkap	varchar
Email	varchar
NoHP	varchar
UserStatusAktif	tinyint
LastLogin	datetime
Avatar	varchar
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
LastPasswordUpdateBy	varchar
LastPasswordUpdateAt	datetime
[idx]
Username	`Username`	UNIQUE	BTREE
IDGroup	`IDGroup`	NORMAL	BTREE


- user_group
IDUserGroup	int
NamaGroup	varchar
Deskripsi	text
StatusAktif	tinyint
AdminOnly	tinyint
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]

- user_group_akses
IDGroupAkses	int
IDUserGroup	int
IDAkses	int
IsAllow	tinyint
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]
IDUserGroup	`IDUserGroup`	NORMAL	BTREE
IDAkses	`IDAkses`	NORMAL	BTREE


- user_login_devices
IDToken	int
IDUser	int
DeviceUUID	varchar
DevicePlatform	varchar
DeviceName	varchar
IPAddress	varchar
UserAgent	text
GPS	varchar
GPS_Lat	decimal
GPS_Lng	decimal
Token	varchar
ExpiryDate	datetime
IsActive	tinyint
CreatedAt	datetime
UpdatedAt	datetime
[idx]
uniq_user_device	`IDUser`, `DeviceUUID`	UNIQUE	BTREE


- tournament
IDTournament	int
NamaTournament	varchar
NamaTournamentEN	varchar
Description	text
Location	varchar
GoogleMapsUrl	varchar
RegistrationStart	datetime
RegistrationEnd	datetime
TournamentStart	datetime
TournamentEnd	datetime
RegistrationFee	decimal
TotalPrize	decimal
MaxParticipant	int
RuleDocument	varchar
RuleDocumentName	varchar
RuleDocumentUploadedBy	varchar
RuleDocumentUploadedAt	datetime
Gambar	varchar
StatusTournament	enum = 'draft','open_registration','closed_registration','ongoing','finished','cancelled'
TournamentFormat	enum = 'round_robin','single_elimination'
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
PointWin	int
PointLose	int
PointDraw	int
ReportGeneratedName	varchar
ReportGeneratedBy	varchar
ReportGeneratedAt	datetime
[idx]
idx_tournament_status_dates	`StatusTournament`, `TournamentStart`, `TournamentEnd`	NORMAL	BTREE
idx_tournament_registration_period	`RegistrationStart`, `RegistrationEnd`	NORMAL	BTREE
idx_tournament_name	`NamaTournament`	NORMAL	BTREE
idx_tournament_format	`TournamentFormat`	NORMAL	BTREE
idx_tournament_status_start	`StatusTournament`, `TournamentStart`	NORMAL	BTREE

- tournament_category_level
IDTCL	int
IDTournament	int
IDKategori	int
IDLevel	int
MaxSlot	int
RegistrationFee	decimal
CreateBy	varchar
CreateAt	datetime
PointWin	int
PointLose	int
PointDraw	int
[idx]
IDLevel	`IDLevel`	NORMAL	BTREE


- tournament_group
IDGroup	bigint
IDTournament	int
IDTCL	int
GroupName	varchar
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]
IDTournament	`IDTournament`	NORMAL	BTREE
IDTCL	`IDTCL`	NORMAL	BTREE


- tournament_group_team
IDGroupTeam	bigint
IDGroup	bigint
IDRegistration	bigint
SeedNumber	int
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]
unique_group_team	`IDGroup`, `IDRegistration`	UNIQUE	BTREE
fk_groupteam_team	`IDRegistration`	NORMAL	BTREE


- tournament_match
IDMatch	bigint
IDTournament	int
IDTCL	int
IDGroup	bigint
RoundNumber	int
MatchNumber	int
TeamA	bigint
TeamB	bigint
WinnerTeam	bigint
Status	enum
MatchDate	datetime
NextMatchID	bigint
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[indexes]
IDTournament	`IDTournament`
IDGroup	`IDGroup`
RoundNumber	`RoundNumber`
fk_match_teamA	`TeamA`
fk_match_teamB	`TeamB`
fk_match_winner	`WinnerTeam`
fk_match_next	`NextMatchID`


- tournament_match_set
IDSet	bigint
IDMatch	bigint
SetNumber	int
ScoreTeamA	int
ScoreTeamB	int
[indexes]
unique_match_set	`IDMatch`, `SetNumber`	UNIQUE	BTREE

- tournament_registration
IDRegistration	bigint
IDTournament	int
IDTCL	int
IDTeam	bigint
PaymentStatus	enum = 'pending','paid','rejected'
PaymentProof	varchar
RegistrationDate	datetime
Status	enum = 'active','cancelled','disqualified','waiting'
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]
uniq_player_once	`IDTournament`, `IDTeam`	UNIQUE	BTREE
IDTCL	`IDTCL`	NORMAL	BTREE
IDTeam	`IDTeam`	NORMAL	BTREE


- tournament_stage
IDTournamentStage	int
IDTCL	int
IDStage	int
StageOrder	int
PointWin	int
PointLose	int
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime
[idx]
uniq_stage_order	`IDTCL`, `StageOrder`, `IDStage`	UNIQUE	BTREE


- tournament_standing
IDStanding	bigint
IDTournament	int
IDGroup	bigint
IDTeam	bigint
Played	int
Win	int
Lose	int
Draw	int
Points	int
ScoreFor	int
ScoreAgainst	int
[idx]
unique_team	`IDTournament`, `IDGroup`, `IDTeam`	UNIQUE	BTREE


- team
IDTeam	bigint
IDTournament	int
IDTCL	int
TeamName	varchar
CreateAt	datetime
[idx]
IDTournament	`IDTournament`	NORMAL	BTREE
IDTCL	`IDTCL`	NORMAL	BTREE

- team_member
IDTeamMember	bigint
IDTeam	bigint
IDPlayer	bigint



CREATE TABLE tournament_point_setup (
    IDPointSetup INT PRIMARY KEY AUTO_INCREMENT,
    IDTournament INT NOT NULL,
    
    PointChampion1 INT DEFAULT 100,
    PointChampion2 INT DEFAULT 50,
    PointChampion3 INT DEFAULT 25,
    PointChampion4 INT DEFAULT 0,
    
    PointMinusFirstRound INT DEFAULT 0,
    PointMinusSecondRound INT DEFAULT 0,
    PointMinusQuarterFinal INT DEFAULT 0,
    
    PointParticipate INT DEFAULT 0,
    
    CreateBy VARCHAR(100),
    CreateAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    LastUpdateBy VARCHAR(100),
    LastUpdateAt DATETIME,
    
    UNIQUE KEY unique_tournament (IDTournament),
    CONSTRAINT fk_tps_tournament FOREIGN KEY (IDTournament) 
        REFERENCES tournament(IDTournament)
);

-- INDEX
CREATE INDEX idx_tps_tournament ON tournament_point_setup(IDTournament);


- ranking_category
IDRankCategory	int
CategoryName	varchar
CategoryCode	varchar
MinPoints	int
MaxPoints	int
Description	text
IsExclusive	tinyint
IsActive	tinyint
IsDefaultRank	tinyint
SortOrder	int
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime

-- INDEX
CREATE INDEX idx_rc_code ON ranking_category(CategoryCode);
CREATE INDEX idx_rc_active ON ranking_category(IsActive);


- player_ranking
IDPlayerRanking	bigint
IDPlayer	bigint
IDRankCategory	int
LastPromotionDate	datetime
PromotedFromCategory	varchar
PromotionReason	text
IsManualOverride	tinyint
IsActive	tinyint
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime



- player_ranking_history
IDHistory	bigint
IDPlayer	bigint
IDRankCategory	int
TotalPoints	int
ChangeType	enum
ChangeReason	text
IsChampionship	tinyint
IsRunnerUp	tinyint
IDTournament	int
ChangeDate	datetime
CreateBy	varchar
CreateAt	datetime
LastUpdateBy	varchar
LastUpdateAt	datetime

 

-- =========================
-- TABLE: ranking_promotion_rule
-- =========================
CREATE TABLE ranking_promotion_rule (
    IDRule INT PRIMARY KEY AUTO_INCREMENT,
    FromCategoryCode VARCHAR(20) NOT NULL,
    ToCategoryCode VARCHAR(20) NOT NULL,
    
    RuleType ENUM('points', 'runner_up_count', 'champion', 'top_ten_ranking', 'manual') NOT NULL,
    RuleValue INT,
    
    ConditionDescription TEXT,
    IsActive TINYINT DEFAULT 1,
    
    CreateBy VARCHAR(100),
    CreateAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    LastUpdateBy VARCHAR(100),
    LastUpdateAt DATETIME
);

-- INDEX
CREATE INDEX idx_rpr_from_to ON ranking_promotion_rule(FromCategoryCode, ToCategoryCode);
CREATE INDEX idx_rpr_type ON ranking_promotion_rule(RuleType);


-- =========================
-- SEED DATA
-- =========================
INSERT INTO ranking_category (CategoryCode, CategoryName, MinPoints, MaxPoints, Description) VALUES
('LB', 'Lower Bronze', 0, 199, 'Kategori pemula'),
('UB', 'Upper Bronze', 200, 399, 'Kategori menengah bawah'),
('SV', 'Silver', 400, NULL, 'Kategori menengah atas'),
('GD', 'Gold', NULL, NULL, 'Kategori eksklusif undangan');


INSERT INTO ranking_promotion_rule (FromCategoryCode, ToCategoryCode, RuleType, RuleValue, ConditionDescription) VALUES
('LB', 'UB', 'points', 200, 'Mencapai 200 poin'),
('LB', 'UB', 'runner_up_count', 2, 'Runner up 2 kali otomatis naik'),
('LB', 'UB', 'champion', 1, 'Juara Lower Bronze otomatis naik dan tidak boleh main LB selama 1 tahun'),
('LB', 'UB', 'top_ten_ranking', 1, 'Top 10 ranking akhir tahun naik ke Upper Bronze'),
('UB', 'SV', 'points', 400, 'Mencapai 400 poin');



-- =========================
-- TABLE: notification
-- =========================
CREATE TABLE notification (
    IDNotification BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Title dan konten notifikasi
    Title VARCHAR(255) NOT NULL,
    Message TEXT NOT NULL,
    
    -- Tipe notifikasi untuk menentukan action/CTA
    NotificationType ENUM(
        'tournament_registration',  -- Pendaftaran tournament
        'membership_payment',       -- Pembayaran membership
        'tournament_approval',      -- Approve tournament
        'payment_verification',     -- Verifikasi pembayaran
        'ranking_promotion',        -- Kenaikan ranking
        'tournament_result',        -- Hasil tournament
        'system_announcement',      -- Pengumuman sistem
        'player_verification',      -- Verifikasi player
        'team_invitation',          -- Undangan tim
        'match_schedule'            -- Jadwal pertandingan
    ) NOT NULL,
    
    -- Kode/referensi untuk redirect
    ReferenceCode VARCHAR(50) NULL COMMENT 'Kode referensi untuk URL redirect',
    ReferenceID VARCHAR(50) NULL COMMENT 'ID referensi (bisa IDTournament, IDPlayer, IDMembershipPayment, dll)',
    
    -- Target audiens
    TargetType ENUM('player', 'admin', 'all') NOT NULL DEFAULT 'all',
    TargetID BIGINT NULL COMMENT 'IDPlayer atau IDUser (NULL jika untuk all)',
    
    -- Metadata tambahan (JSON untuk fleksibilitas)
    Metadata JSON NULL COMMENT 'Data tambahan dalam format JSON',
     
    -- URL redirect (bisa di-generate dari ReferenceCode + ReferenceID)
    RedirectURL VARCHAR(500) NULL COMMENT 'URL lengkap untuk redirect (optional)',
     
    ExpiredDate DATETIME NULL,
    
    -- Audit trail
    CreateBy VARCHAR(100),
    CreateAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    LastUpdateBy VARCHAR(100),
    LastUpdateAt DATETIME,
    
    -- Indexes
    INDEX idx_notification_target (TargetType, TargetID),
    INDEX idx_notification_type (NotificationType), 
    INDEX idx_notification_reference (ReferenceCode, ReferenceID),
    INDEX idx_notification_expired (ExpiredDate)
);


-- =========================
-- TABLE: notification_read (Track read status per user)
-- =========================
CREATE TABLE notification_read (
    IDNotificationRead BIGINT PRIMARY KEY AUTO_INCREMENT,
    IDNotification BIGINT NOT NULL,
    
    -- Target pembaca
    ReaderType ENUM('player', 'admin') NOT NULL,
    ReaderID BIGINT NOT NULL COMMENT 'IDPlayer atau IDUser',
    
    -- Status baca
    IsRead TINYINT DEFAULT 0,
    ReadDate DATETIME NULL,
    
    -- Timestamp
    CreateAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    LastUpdateAt DATETIME,
    
    -- Unique constraint untuk menghindari duplikasi
    UNIQUE KEY unique_reader_notification (IDNotification, ReaderType, ReaderID),
    
    -- Foreign key
    CONSTRAINT fk_notification_read_notification FOREIGN KEY (IDNotification) 
        REFERENCES notification(IDNotification) ON DELETE CASCADE,
    
    -- Indexes
    INDEX idx_reader (ReaderType, ReaderID),
    INDEX idx_read_status (IsRead),
    INDEX idx_read_date (ReadDate)
);
 